package com.valentinalee.bms.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;

import com.valentinalee.bms.dao.StudentDao;
import com.valentinalee.bms.entity.Student;
import com.valentinalee.bms.util.DBUtil;
import com.valentinalee.bms.util.MybatisSqlSessionUtil;

public class StudentDaoImpl implements StudentDao {

	public Student getById(String id) throws SQLException {
		// 1.获取session
		SqlSession session = MybatisSqlSessionUtil.getSqlSession();
		Student stu = session.selectOne("getById", id);

		return stu;
	}

	public Student add(Student vo) throws SQLException {
		
		Connection con=DBUtil.getConnection();
		String sql="insert into sys_student(cid,cno,cname,csex,enterday,graduateday) values(?,?,?,?,?,?)";
		try{
			PreparedStatement ps=con.prepareStatement(sql);
			int i=1;
			vo.setId(DBUtil.uuid());
			ps.setString(i++,vo.getId());
			ps.setString(i++, vo.getNo());
			ps.setString(i++, vo.getName());
			ps.setString(i++, vo.getSex());
			ps.setDate(i++, new Date(vo.getEnterDay().getTime()));
			ps.setDate(i++, new Date(vo.getGraduateDay().getTime()));
			ps.executeUpdate();
			// 提交事务
			con.commit();
		}catch(SQLException e){
			// 事务回滚
			con.rollback();
			throw e;
		}finally{
			DBUtil.close(con);
		}
		return vo;
	}

	public boolean isExsitNo(String no)throws SQLException {
		Connection con=DBUtil.getConnection();
		String sql="select cno from sys_student where cno=?";
		try{
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setString(1, no);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				return true;
			}
		}finally{
			DBUtil.close(con);
		}
		return false;
	}
	

	public int  queryByKey(String key, int pageSize, int pageNo,List<Student> pageData)
			throws SQLException {
		int totalCount = 0;

		Map<String, Object> map = new HashMap<String, Object>();
		map.put("key", key);
		// 查询总条数
		SqlSession session = MybatisSqlSessionUtil.getSqlSession();
		totalCount = session.selectOne("countByKey", map);
		System.out.println(totalCount);
		
		// 查询列表
		map.put("startIndex", (pageNo - 1) * pageSize);
		map.put("rows", pageSize);
		List<Student> list = session.selectList("listByKey", map);
		pageData.addAll(list);
		
		/*
		 * 如何传参： 1.map：key 2.对象：属性名 3.注解@Param
		 */

		return totalCount;
	}
	


	public int deleteById(String id) throws SQLException {
		int n=0;
		Connection con=DBUtil.getConnection();
		String sql="delete from sys_student where cid=?";
		try{
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setString(1, id);
			n=ps.executeUpdate();
			con.commit();
		}catch(SQLException e){
			con.rollback();
			throw e;
		}finally{
			DBUtil.close(con);
		}
		return n;
	}

	public void update(Student vo) throws SQLException {
		Connection con=DBUtil.getConnection();
		String sql="update sys_student set cno=?,cname=?,csex=?,enterday=?,graduateday=? where cid=?";
		try{
			PreparedStatement ps=con.prepareStatement(sql);
			int i=1;
			ps.setString(i++, vo.getNo());
			ps.setString(i++, vo.getName());
			ps.setString(i++, vo.getSex());
			ps.setDate(i++, new Date(vo.getEnterDay().getTime()));
			ps.setDate(i++, new Date(vo.getGraduateDay().getTime()));
			ps.setString(i++,vo.getId());
			ps.executeUpdate();
			// 提交事务
			con.commit();
		}catch(SQLException e){
			// 事务回滚
			con.rollback();
			throw e;
		}finally{
			DBUtil.close(con);
		}
	}

	public int getMaxNo() throws SQLException {
		int maxNo=0;
		Connection con=DBUtil.getConnection();
		String sql="select max(cno) from sys_student";
		try{
			PreparedStatement ps=con.prepareStatement(sql);
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				String str=rs.getString(1);
				maxNo=str==null?100000:Integer.parseInt(str);
			}
		}finally{
			DBUtil.close(con);
		}
		return maxNo+1;
	}

}
